package com.snbc.hbase.migration.db;
import java.io.IOException;
import java.io.DataInput;
import java.io.DataOutput;
import java.io.IOException;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Properties;

import com.snbc.format.MyDBOutputFormat;
import com.snbc.util.PropertiesUtil;
import org.apache.hadoop.conf.Configuration;
import org.apache.hadoop.hbase.client.Result;
import org.apache.hadoop.hbase.client.Scan;
import org.apache.hadoop.hbase.io.ImmutableBytesWritable;
import org.apache.hadoop.hbase.mapreduce.TableMapReduceUtil;
import org.apache.hadoop.hbase.mapreduce.TableMapper;
import org.apache.hadoop.hbase.util.Bytes;
import org.apache.hadoop.io.Text;
import org.apache.hadoop.io.Writable;
import org.apache.hadoop.mapreduce.Job;
import org.apache.hadoop.mapreduce.Reducer;
import org.apache.hadoop.mapreduce.lib.db.DBConfiguration;
import org.apache.hadoop.mapreduce.lib.db.DBWritable;
import org.apache.log4j.BasicConfigurator;

/**
 * @author shichenguang
 * @desciption hbase 数据迁移到db2
 * @date 2019-09-06 11:32
 */
public class DataToDB2 {
    private static Properties pro= PropertiesUtil.getPro();
    /**
     * 重写DBWritable
     * @author Alich
     * TblsWritable需要向db2中写入数据
     */
    public static class TblsWritable implements Writable, DBWritable {
        String name;
        String age;
        String id;

        public TblsWritable() {

        }

        public TblsWritable(String name,String age,String id) {
            this.name = name;
            this.age = age;
            this.id = id;
        }


        public void write(PreparedStatement statement) throws SQLException {
            statement.setString(1, this.name);
            statement.setString(2, this.age);
            statement.setString(3, this.id);
        }


        public void readFields(ResultSet resultSet) throws SQLException {
            this.name = resultSet.getString(1);
            this.age = resultSet.getString(2);
            this.id = resultSet.getString(3);
        }


        public void write(DataOutput out) throws IOException {
            out.writeUTF(this.name);
            out.writeUTF(this.age);
            out.writeUTF(this.id);
        }


        public void readFields(DataInput in) throws IOException {
            this.name = in.readUTF();
            this.age = in.readUTF();
            this.id = in.readUTF();
        }
        /**
         * 重写DBWritable
         * @author asheng
         * TblsWritable需要向db2中写入数据
         */
        public static class ConnDB2Mapper extends TableMapper<Text, Text> {
            //TblsRecord是自定义的类型，也就是上面重写的DBWritable类
            @Override
            public void map(ImmutableBytesWritable row, Result value, Context context)throws IOException,InterruptedException {
                //<首字母偏移量,该行内容>接收进来，然后处理value，将abc和x作为map的输出
                //key对于本程序没有太大的意义，没有使用
                String name=Bytes.toString(value.getValue(Bytes.toBytes("info"),Bytes.toBytes("name")));
                String id=Bytes.toString(value.getValue(Bytes.toBytes("info"),Bytes.toBytes("id")));
                String age=Bytes.toString(value.getValue(Bytes.toBytes("info"),Bytes.toBytes("age")));

                context.write(new Text(id),new Text(name+"_"+age+"_"+id));
            }

        }


        public static class ConnDB2Reducer extends Reducer<Text, Text,TblsWritable,TblsWritable>
        {
            @Override
            public void reduce(Text key,Iterable<Text> values,Context context)throws IOException,InterruptedException {
                Iterator<Text> itr =values.iterator();
                String[] recordsTwo = null;
                String[] itemOne = null;

//                context.write(new Text(id),new Text(name+"_"+age+"_"+id));
                String name = "";
                String age = "";
                String id = "";
                while(itr.hasNext()){
                    itemOne = itr.next().toString().split("_");
                    name = itemOne[0];
                    age = itemOne[1];
                    id = itemOne[2];
                }

                TblsWritable bean = new TblsWritable(name,age,id);

                //mapReduce
                context.write(bean, null);
            }
        }
    }



    public static void main(String[] args)  {
        try {
           BasicConfigurator.configure();
            insertDB2();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private static void insertDB2() throws IOException, ClassNotFoundException, InterruptedException {
        Configuration conf = new Configuration();

        DBConfiguration.configureDB(conf, pro.getProperty("jdbc.driver"), pro.getProperty("jdbc.url"), pro.getProperty("jdbc.username"), pro.getProperty("jdbc.password"));



        Scan scan = new Scan();
        scan.addFamily(Bytes.toBytes("info"));
        scan.setCaching(PropertiesUtil.getCacheSize()); // 1 is the default in Scan, which will be bad for
        scan.setCacheBlocks(false);
        // MapReduce jobs
        scan.setCacheBlocks(false); // don't set to true for MR jobs


        Job job = Job.getInstance(conf);
        job.setJobName("shichenguangTest");
        job.setJarByClass(DataToDB2.class);   //这里一定一定要注意啊！！！！！！！！！！！！！！！！！！！！！


        TableMapReduceUtil.initTableMapperJob("student", // input table
                scan, // Scan instance to control CF and attribute selection
                TblsWritable.ConnDB2Mapper.class, // mapper class
                Text.class, // mapper output key
                Text.class, // mapper output value
                job);
        TableMapReduceUtil.addDependencyJars(job);
        TableMapReduceUtil.addDependencyJars(job.getConfiguration());
        job.setReducerClass(TblsWritable.ConnDB2Reducer.class);
        job.setOutputKeyClass(Text.class);
        job.setOutputValueClass(Text.class);

        job.setOutputFormatClass(MyDBOutputFormat.class);
        MyDBOutputFormat.setOutput(job,"student","name","age","id");
        System.exit(job.waitForCompletion(true) ? 0 : 1);
    }
}
